﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SQLite;

namespace FingerprintAttendance
{
  public  class DBHelper
    {
        //定义连接数据库字符串
        //private static string strConn = "Data Source=127.0.0.1;User ID=root;Password=tyh12345;DataBase=test;Charset=gb2312;";
        private static string strConn = ConfigurationManager.AppSettings["ConnectionString"].ToString();
        //定义公有连接对象
        public static SQLiteConnection conn = new SQLiteConnection(strConn);

        public static int command_timeout = 180;

        /// <summary>
        /// 获取第一行第一列的内容
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string strSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(strSql, conn);
            conn.Open();
            object obj = cmd.ExecuteScalar();
            conn.Close();
            return obj;
        }

        public static int ExecuteNonQuery(string strSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(strSql, conn);
            conn.Open();
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }

        public static int ExecuteSqlWithParams(string strSql, params SQLiteParameter[] cmdParms)
        {

            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.CommandTimeout = command_timeout;
                try
                {
                    PrepareCommand(cmd, conn, null, strSql, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    conn.Close();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    conn.Close();
                    throw new Exception(E.Message);
                }

            }

        }

        private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandTimeout = command_timeout;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;  
            if (cmdParms != null)
            {
                foreach (SQLiteParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        public static SQLiteDataReader ExecuteReader(string strSql)
        {
            SQLiteCommand cmd = new SQLiteCommand(strSql, conn);
            conn.Open();
            SQLiteDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            //DBHelper.conn.Close();
            return reader;
        }

        public static DataSet GetDataSet(string strSql)
        {
            SQLiteDataAdapter da = new SQLiteDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            return ds;
        }

        public static SQLiteDataAdapter GetDataSet(string strSql, DataSet ds)
        {

            SQLiteDataAdapter da = new SQLiteDataAdapter(strSql, conn);
            da.Fill(ds);
            
            //然后用SqlCommandBuilder自动为SqlDataAdapter生成Insert、Update、Delete命令 
            SQLiteCommandBuilder sqlCmdBuilder = new SQLiteCommandBuilder(da);
            return da;
        }

    }
}
